﻿



create proc [Projects].[SaveProjectValues]
@ProjectID			int,
--Values
@ErloesSuHw			decimal(11,2), 
@ErloesSuPr			decimal(11,2), 
@ErloesSuMo			decimal(11,2),
@ErloesRaPr			decimal(11,2), 
@ErloesRaLe			decimal(11,2), 
@ErloesRaPro		decimal(11,2), 
@ErloesSf			decimal(11,2), 
@ErloesEm			decimal(11,2), 
@ErloesFa			decimal(11,2),
@CostHandelsware	decimal(11,2), 
@CostProduktion		decimal(11,2), 
@CostMontage		decimal(11,2), 
@CostAdministration	decimal(11,2), 
@ExtraCostBIPercent	decimal(11,2), 
@ExtraCostBIFix		decimal(11,2), 
@ExtraCostPrEPercent	decimal(11,2), 
@ExtraCostPrEFix	decimal(11,2), 
@ExtraCostErfGar	decimal(11,2), 
@ExtraCostHaftRuecklage	decimal(11,2), 
@ExtraCostSkonto	decimal(11,2), 
@ExtraCostProvisionDB	decimal(11,2), 
@TimeProduktion		decimal(11,2), 
@TimeMontage		decimal(11,2), 
@TimeAdministration	decimal(11,2),
@TimeProducts		decimal(11,2),
@UserID				int

as begin
	set nocount on;

	declare @logText nvarchar(255),
			@rc int;


	set xact_abort on

	begin tran
	
	begin try


		;with valDetails
		as
		(
			select	@ProjectID				'ProjectID',
					@ErloesSuHw				'ErloesSuHw', 
					@ErloesSuPr				'ErloesSuPr', 
					@ErloesSuMo				'ErloesSuMo',
					@ErloesRaPr				'ErloesRaPr', 
					@ErloesRaLe				'ErloesRaLe', 
					@ErloesRaPro			'ErloesRaPro', 
					@ErloesSf				'ErloesSf', 
					@ErloesEm				'ErloesEm', 
					@ErloesFa				'ErloesFa',
					@CostHandelsware		'CostHandelsware', 
					@CostProduktion			'CostProduktion', 
					@CostMontage			'CostMontage', 
					@CostAdministration		'CostAdministration', 
					@ExtraCostBIPercent		'ExtraCostBIPercent', 
					@ExtraCostBIFix			'ExtraCostBIFix', 
					@ExtraCostPrEPercent	'ExtraCostPrEPercent', 
					@ExtraCostPrEFix		'ExtraCostPrEFix', 
					@ExtraCostErfGar		'ExtraCostErfGar', 
					@ExtraCostHaftRuecklage	'ExtraCostHaftRuecklage', 
					@ExtraCostSkonto		'ExtraCostSkonto', 
					@ExtraCostProvisionDB	'ExtraCostProvisionDB', 
					@TimeProduktion			'TimeProduktion', 
					@TimeMontage			'TimeMontage', 
					@TimeProducts			'TimeProducts',
					@TimeAdministration		'TimeAdministration'
		)
		merge into [Projects].[datProjectsValues] t
		using valDetails s on
			s.ProjectID = t.ProjectID
		when not matched by target then
		insert (
				ProjectID, 
				ErloesSuHw, 
				ErloesSuPr, 
				ErloesSuMo, 
				ErloesRaPr, 
				ErloesRaLe, 
				ErloesRaPro, 
				ErloesSf, 
				ErloesEm, 
				ErloesFa, 
				CostHandelsware, 
				CostProduktion, 
				CostMontage, 
				CostAdministration, 
				ExtraCostBIPercent, 
				ExtraCostBIFix, 
				ExtraCostPrEPercent, 
				ExtraCostPrEFix, 
				ExtraCostErfGar, 
				ExtraCostHaftRuecklage, 
				ExtraCostSkonto, 
				ExtraCostProvisionDB, 
				TimeProduktion, 
				TimeMontage, 
				TimeProducts,
				TimeAdministration
				)
		values (
				s.ProjectID, 
				s.ErloesSuHw, 
				s.ErloesSuPr, 
				s.ErloesSuMo, 
				s.ErloesRaPr, 
				s.ErloesRaLe, 
				s.ErloesRaPro, 
				s.ErloesSf, 
				s.ErloesEm, 
				s.ErloesFa, 
				s.CostHandelsware, 
				s.CostProduktion, 
				s.CostMontage, 
				s.CostAdministration, 
				s.ExtraCostBIPercent, 
				s.ExtraCostBIFix, 
				s.ExtraCostPrEPercent, 
				s.ExtraCostPrEFix, 
				s.ExtraCostErfGar, 
				s.ExtraCostHaftRuecklage, 
				s.ExtraCostSkonto, 
				s.ExtraCostProvisionDB, 
				s.TimeProduktion, 
				s.TimeMontage, 
				s.TimeProducts,
				s.TimeAdministration
				)
		when matched then
		update
			set
				ErloesSuHw				= s.ErloesSuHw, 
				ErloesSuPr				= s.ErloesSuPr, 
				ErloesSuMo				= s.ErloesSuMo, 
				ErloesRaPr				= s.ErloesRaPr, 
				ErloesRaLe				= s.ErloesRaLe, 
				ErloesRaPro				= s.ErloesRaPro, 
				ErloesSf				= s.ErloesSf, 
				ErloesEm				= s.ErloesEm, 
				ErloesFa				= s.ErloesFa, 
				CostHandelsware			= s.CostHandelsware, 
				CostProduktion			= s.CostProduktion, 
				CostMontage				= s.CostMontage, 
				CostAdministration		= s.CostAdministration, 
				ExtraCostBIPercent		= s.ExtraCostBIPercent, 
				ExtraCostBIFix			= s.ExtraCostBIFix, 
				ExtraCostPrEPercent		= s.ExtraCostPrEPercent, 
				ExtraCostPrEFix			= s.ExtraCostPrEFix, 
				ExtraCostErfGar			= s.ExtraCostErfGar, 
				ExtraCostHaftRuecklage	= s.ExtraCostHaftRuecklage, 
				ExtraCostSkonto			= s.ExtraCostSkonto, 
				ExtraCostProvisionDB	= s.ExtraCostProvisionDB, 
				TimeProduktion			= s.TimeProduktion, 
				TimeMontage				= s.TimeMontage, 
				TimeProducts			= s.TimeProducts,
				TimeAdministration		= s.TimeAdministration
				;

		end try
		begin catch

			Select @ProjectID 'ProjectID', -1 'Return', ERROR_MESSAGE() 'Error' 
			goto WithError
		end catch

		select @ProjectID 'ProjectID', 0 'Return', null 'Error'

		commit tran
	
		return 0

WithError:
	if @@TRANCOUNT > 0
	begin
		set @logText = 'Projects.SaveProject: ' + cast(@ProjectID as char(10))
		exec @rc = dbo.SaveLog
			@UserID = @UserID,
			@LogType = 'S',
			@LogArea = 'Pr',
			@LogDetails = @logText,
			@IsError = 1
		rollback tran
	end
	return -1
end
GO
GRANT EXECUTE
    ON OBJECT::[Projects].[SaveProjectValues] TO [TpOfficeUser]
    AS [TORPROFI\SQL_TpUsers];

